package com.han.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.han.bom.Question;

public class AnswerDAO {
	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static Logger logger = Logger.getLogger(AnswerDAO.class);
	
	public void connect() throws ClassNotFoundException, SQLException{
		try{
			Class.forName("com.mysql.jdbc.Driver");
			this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/beta_test_engine", "root", "");
		}catch(ClassNotFoundException e){
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			e.printStackTrace();
			throw e;
		}
	}
	
	public void disconnect() throws SQLException{
		if(this.resultSet!=null){
			try{
				this.resultSet.close();
			}catch(SQLException e){
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.preparedStatement!=null){
			try {
				this.preparedStatement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.connection!=null){
			try {
				this.connection.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
	}
	
	public ArrayList<String> retrieveAnswersByQuestion(String questionid) throws SQLException, ClassNotFoundException{
		ArrayList<String> choices = new ArrayList<String>();
		int qid = 0;
		String choice = "";
		
		try{
			qid = Integer.parseInt(questionid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			qid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_choice WHERE question_id=? ORDER BY answer_iscorrect DESC";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, qid);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(this.resultSet.next()){
				choice = this.resultSet.getString("answer");
				choices.add(choice);
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return choices;
	}
	
	public boolean addAnswerToQuestion(Question question) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String insert = "INSERT IGNORE INTO tbl_choice(answer_id,answer,question_id,answer_iscorrect) VALUES(?,?,?,?)";
		int questionid = Integer.parseInt(question.getQuestionID().replaceAll("[^\\d]", ""));
		int i = 0;
		int row=0;
		
		try{
			questionid = Integer.parseInt(question.getQuestionID().replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			questionid = 0;
		}
		
		try {
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			for(String answer: question.getChoices()){
				String ansid = ""+question.getQuestionID()+(i+1)+"A";
				this.preparedStatement.setString(1, ansid);
				this.preparedStatement.setString(2, answer);
				this.preparedStatement.setInt(3, questionid);
				
				if(question.getAnswer().equals(answer)){
					this.preparedStatement.setBoolean(4, true);
				}else{
					this.preparedStatement.setBoolean(4, false);
				}
				row +=this.preparedStatement.executeUpdate();
				i++;
			}
			
			success = (row==question.getChoices().size())?true:false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateAnswer(String answerid, String answer, boolean iscorrect) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String update = "UPDATE tbl_choice SET answer = ?,answer_iscorrect=? WHERE answer_id=?";
		
		try {
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(update);
			
			this.preparedStatement.setString(1, answer);
			this.preparedStatement.setBoolean(2, iscorrect);
			this.preparedStatement.setString(3, answerid);
			this.preparedStatement.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean removeAnswerFromQuestion(String answerid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String delete = "DELETE FROM tbl_choice WHERE answer_id = ?";
		
		try {
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(delete);
			
			this.preparedStatement.setString(1, answerid);
			this.preparedStatement.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return success;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public PreparedStatement getPreparedStatement() {
		return preparedStatement;
	}

	public void setPreparedStatement(PreparedStatement preparedStatement) {
		this.preparedStatement = preparedStatement;
	}

	public ResultSet getResultSet() {
		return resultSet;
	}

	public void setResultSet(ResultSet resultSet) {
		this.resultSet = resultSet;
	}
}
